package com.tianqu.tms.www.statistics.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TmsTableNames;
import com.tianqu.tms.www.statistics.dto.ComboDto;
import com.tianqu.tms.www.statistics.dto.TradeTransferDto;

public class TmsTransferAreaDayCountDao extends TmsDao{

	public TmsTransferAreaDayCountDao() throws TmsDaoException {
		super();
	}

	/**
	 * 十三、流出省TOP5
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferDto getTop5OfProv()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferDto tradeTransferDto = new TradeTransferDto();
		
		List<ComboDto> comboDtoList = new ArrayList<ComboDto>();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" tc.proName, IFNULL(sum(ttadc.transferNum), 0) as transferNumProvTotal ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_AREA_DAY_COUNT);
			sql.append(" ttadc ");
			sql.append(" left join ");
			sql.append(TmsTableNames.TMS_CITY);
			sql.append(" tc on tc.provinceId = ttadc.carMarketInProv and tc.cityId = ttadc.carMarketInCity"); 
			sql.append(" where ttadc.countDate BETWEEN date_format(date_sub(CURDATE(),interval 90 day),'%Y-%m-%d') and date_format(CURDATE(),'%Y-%m-%d') ");
			sql.append(" group by ttadc.carMarketInProv ");
			sql.append(" order by transferNumProvTotal DESC ");
			sql.append(" limit 0,5 ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			// 循环前5条数据
			while (rs.next()) {
				ComboDto ComboDto = new ComboDto();
				// 今天交易过户量
				ComboDto.setName(rs.getString("proName"));
				// 今天商户交易过户量
				ComboDto.setValue(rs.getString("transferNumProvTotal"));
				
				comboDtoList.add(ComboDto);
			}
			
			tradeTransferDto.setTop5OfProv(comboDtoList);
			
			return tradeTransferDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 十四、流出本省城市TOP5
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferDto getTop5OfCity()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferDto tradeTransferDto = new TradeTransferDto();
		
		List<ComboDto> comboDtoList = new ArrayList<ComboDto>();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" tc.cityName, IFNULL(sum(ttadc.transferNum), 0) as transferNumCityTotal ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_AREA_DAY_COUNT);
			sql.append(" ttadc ");
			sql.append(" left join ");
			sql.append(TmsTableNames.TMS_CITY);
			sql.append(" tc on tc.provinceId = ttadc.carMarketInProv and tc.cityId = ttadc.carMarketInCity"); 
			sql.append(" where ttadc.countDate BETWEEN date_format(date_sub(CURDATE(),interval 90 day),'%Y-%m-%d') and date_format(CURDATE(),'%Y-%m-%d') ");
			sql.append(" and ttadc.carMarketInProv = '1001' ");// 辽宁省
			sql.append(" group by ttadc.carMarketInProv,ttadc.carMarketInCity ");
			sql.append(" order by transferNumCityTotal DESC ");
			sql.append(" limit 0,5 ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			// 循环前5条数据
			while (rs.next()) {
				ComboDto ComboDto = new ComboDto();
				// 今天交易过户量
				ComboDto.setName(rs.getString("cityName"));
				// 今天商户交易过户量
				ComboDto.setValue(rs.getString("transferNumCityTotal"));
				
				comboDtoList.add(ComboDto);
			}
			
			tradeTransferDto.setTop5OfCity(comboDtoList);
			
			return tradeTransferDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
}
